use sqlx::{Executor, SqliteConnection};

use super::get_pool;
use crate::{error::SqlxErr, utils::current_timestamp};
use sqlx::Row;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_replenish (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id INTEGER NOT NULL,  
    replenish_timestamp INTEGER NOT NULL,
    insert_timestamp INTEGER NOT NULL,
    UNIQUE (device_id, replenish_timestamp),
    FOREIGN KEY (device_id) REFERENCES tb_device(id) ON DELETE CASCADE
)
"#;

// replenish_timestamp 客户端补货时间戳  可以用于实现幂等
// insert_timestamp 服务器数据创建时的时间戳  由于网络 可能导致两者不一样

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

pub async fn is_exists(
    tx: &mut SqliteConnection,
    device_id: i64,
    replenish_timestap: u64,
) -> Result<Option<i64>, SqlxErr> {
    let row = sqlx::query(
        r#"SELECT id FROM tb_replenish WHERE device_id = ? AND replenish_timestamp = ?"#,
    )
    .bind(device_id)
    .bind(replenish_timestap as i64)
    .fetch_optional(tx)
    .await?;
    Ok(row.and_then(|r| r.get(0)))
}

pub async fn create(
    tx: &mut SqliteConnection,
    device_id: i64,
    replenish_timestap: u64,
) -> Result<i64, SqlxErr> {
    let ret =
        sqlx::query(r#"INSERT INTO tb_replenish 
            (device_id, replenish_timestamp, insert_timestamp) 
            VALUES (?, ?, ?)"#)
            .bind(device_id)
            .bind(replenish_timestap as i64)
            .bind(current_timestamp() as i64)
            .execute(tx)
            .await?;
    Ok(ret.last_insert_rowid())
}

pub async fn get_latest_replenish_id(
    tx: &mut SqliteConnection,
    device_id: i64,
) -> Result<Option<i64>, SqlxErr> {
    let row = sqlx::query(
        r#"
        SELECT id FROM tb_replenish WHERE device_id = ? AND insert_timestamp = (
            SELECT MAX(insert_timestamp) FROM tb_replenish WHERE device_id = ?
        ) LIMIT 1
    "#,
    )
    .bind(device_id)
    .bind(device_id)
    .fetch_optional(tx)
    .await?;

    Ok(row.and_then(|row| row.get(0)))
}
